IF OBJECT_ID('dbo.bc_sp_Sys_Get_RefData') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.bc_sp_Sys_Get_RefData
    IF OBJECT_ID('dbo.bc_sp_Sys_Get_RefData') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.bc_sp_Sys_Get_RefData >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.bc_sp_Sys_Get_RefData >>>'
END
go
create procedure bc_sp_Sys_Get_RefData
	@data_name		char(32),
	@ref_group_cd	     char(6),
	@language_id	     smallint,
	@category_cd	     char(6),
	@code_eff_date      smalldatetime,
	@category_type_cd	char(3),
	@list_other_flds	char(1) = 'N'
as
--added Pia on Jan 21 2004 for Emarine GZ Uat item P-036
if rtrim(@data_name) = 'PACKING-TYPE-CODE'
	or rtrim(@data_name) = 'ENDORSEMENT-REASON-CODE'
	or rtrim(@data_name) = 'COMMODITY-CODE'
	begin
		if not exists(select * from treftab
 		                 where data_element_name = @data_name
 		                 --Tim Xu GOALD Consolidation June 25th 2008
  		                --and ref_group_cd = @ref_group_cd
  		                and (ref_group_cd=@ref_group_cd or ref_group_cd='729000' or rtrim(@ref_group_cd)=null)
  		                --End Tim Xu GOALD Consolidation June 25th 2008
   		               and language_id = @language_id
     		             and (category_cd = @category_cd or rtrim(@category_cd) = null)
					   and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
  		                and @code_eff_date between code_eff_date and code_exp_date
						and source_system_id <> 'M2')
		begin
  		  select @language_id = 1
		end

		if @list_other_flds = 'Y'
			select data_element_code, ele_value_desc_txt, corp_equiv_cd
			    from treftab
			    where data_element_name = @data_name
	                 --Tim Xu GOALD Consolidation June 25th 2008
	                --and ref_group_cd = @ref_group_cd
	                and (ref_group_cd=@ref_group_cd or ref_group_cd='729000' or rtrim(@ref_group_cd)=null)
	                --End Tim Xu GOALD Consolidation June 25th 2008
			    and language_id = @language_id
			    and (category_cd = @category_cd or rtrim(@category_cd) = null)
			    and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
			    and @code_eff_date between code_eff_date and code_exp_date
				and source_system_id <> 'M2'

/*=====================================================
 Sort Order 44 Modifications - Original Codes
 Cary Santos
 4/13/2004
=====================================================*/

                --order by data_element_code, ele_value_desc_txt

/*=====================================================
 Sort Order 44 Modifications - End Original Codes
=====================================================*/

/*=====================================================
 Sort Order 44 Modifications - Changed Order By Codes
 Cary Santos
 4/13/2004
=====================================================*/

                order by data_element_code, SORTKEY(ele_value_desc_txt)

/*=====================================================
 Sort Order 44 Modifications - End Changed Codes
=====================================================*/


		else
--jianxing ITSR005384
--			select data_element_code, ele_value_desc_txt
            select distinct data_element_code, ele_value_desc_txt
			    from treftab
			    where data_element_name = @data_name
 		                 --Tim Xu GOALD Consolidation June 25th 2008
  		                --and ref_group_cd = @ref_group_cd
  		                and (ref_group_cd=@ref_group_cd or ref_group_cd='729000' or rtrim(@ref_group_cd)=null)
  		                --End Tim Xu GOALD Consolidation June 25th 2008
			    and language_id = @language_id
			    and (category_cd = @category_cd or rtrim(@category_cd) = null)
			    and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
			    and @code_eff_date between code_eff_date and code_exp_date
				and source_system_id <> 'M2'

/*=====================================================
 Sort Order 44 Modifications - Original Codes
 Cary Santos
 4/13/2004
=====================================================*/

                --order by data_element_code, ele_value_desc_txt

/*=====================================================
 Sort Order 44 Modifications - End Original Codes
=====================================================*/

/*=====================================================
 Sort Order 44 Modifications - Changed Order By Codes
 Cary Santos
 4/13/2004
=====================================================*/

                order by data_element_code, SORTKEY(ele_value_desc_txt)

/*=====================================================
 Sort Order 44 Modifications - End Changed Codes
=====================================================*/
		end
else
	begin
		if not exists(select * from treftab
 		                 where data_element_name = @data_name
 		                 --Tim Xu GOALD Consolidation June 25th 2008
  		                --and ref_group_cd = @ref_group_cd
  		                and (ref_group_cd=@ref_group_cd or ref_group_cd='729000' or rtrim(@ref_group_cd)=null)
  		                --End Tim Xu GOALD Consolidation June 25th 2008
   		               and language_id = @language_id
     		             and (category_cd = @category_cd or rtrim(@category_cd) = null)
					   and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
  		                and @code_eff_date between code_eff_date and code_exp_date)
		begin
  		  select @language_id = 1
		end

		if @list_other_flds = 'Y'
			select data_element_code, ele_value_desc_txt, corp_equiv_cd
			    from treftab
			    where data_element_name = @data_name
 		                 --Tim Xu GOALD Consolidation June 25th 2008
  		                --and ref_group_cd = @ref_group_cd
  		                and (ref_group_cd=@ref_group_cd or ref_group_cd='729000' or rtrim(@ref_group_cd)=null)
  		                --End Tim Xu GOALD Consolidation June 25th 2008
			    and language_id = @language_id
			    and (category_cd = @category_cd or rtrim(@category_cd) = null)
			    and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
			    and @code_eff_date between code_eff_date and code_exp_date

/*=====================================================
 Sort Order 44 Modifications - Original Codes
 Cary Santos
 4/13/2004
=====================================================*/

                --order by data_element_code, ele_value_desc_txt

/*=====================================================
 Sort Order 44 Modifications - End Original Codes
=====================================================*/

/*=====================================================
 Sort Order 44 Modifications - Changed Order By Codes
 Cary Santos
 4/13/2004
=====================================================*/

                order by data_element_code, SORTKEY(ele_value_desc_txt)

/*=====================================================
 Sort Order 44 Modifications - End Changed Codes
=====================================================*/

		else
			select data_element_code, ele_value_desc_txt
			    from treftab
			    where data_element_name = @data_name
 		                 --Tim Xu GOALD Consolidation June 25th 2008
  		                --and ref_group_cd = @ref_group_cd
  		                and (ref_group_cd=@ref_group_cd or ref_group_cd='729000' or rtrim(@ref_group_cd)=null)
  		                --End Tim Xu GOALD Consolidation June 25th 2008
			    and language_id = @language_id
			    and (category_cd = @category_cd or rtrim(@category_cd) = null)
			    and (category_type_cd = @category_type_cd or rtrim(@category_type_cd) = null)
			    and @code_eff_date between code_eff_date and code_exp_date

/*=====================================================
 Sort Order 44 Modifications - Original Codes
 Cary Santos
 4/13/2004
=====================================================*/

                --order by data_element_code, ele_value_desc_txt

/*=====================================================
 Sort Order 44 Modifications - End Original Codes
=====================================================*/

/*=====================================================
 Sort Order 44 Modifications - Changed Order By Codes
 Cary Santos
 4/13/2004
=====================================================*/

                order by data_element_code, SORTKEY(ele_value_desc_txt)

/*=====================================================
 Sort Order 44 Modifications - End Changed Codes
=====================================================*/

	end

go
IF OBJECT_ID('dbo.bc_sp_Sys_Get_RefData') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.bc_sp_Sys_Get_RefData >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.bc_sp_Sys_Get_RefData >>>'
go
EXEC sp_procxmode 'dbo.bc_sp_Sys_Get_RefData','unchained'
go
GRANT EXECUTE ON dbo.bc_sp_Sys_Get_RefData TO pagroup
go
GRANT EXECUTE ON dbo.bc_sp_Sys_Get_RefData TO s3bapp
go
GRANT EXECUTE ON dbo.bc_sp_Sys_Get_RefData TO s3benq
go
